<?php
    require_once '../data/DataSet.php';
    
    /**
     *	TableAdapter class
     *	Provides an interface to a database table, returning a DataSet as a result object.
     *	@author	Simon Copsey <simon.copsey@gmail.com>
     *	@version 0.3
     *	@package data
     */
    class TableAdapter {
        // TODO: these should not be hard coded!
	/**
	 * 'person' table name, for use as parameter $aTable with constructor.
	 * @global String $TABLE_PERSON
	 */ 
	public static $TABLE_PERSON = "person";	
	/**
	 * 'account' table name, for use as parameter $aTable with constructor.
	 * @global String $TABLE_ACCOUNT
	 */ 
	public static $TABLE_ACCOUNT = "account";
	/**
	 * 'widget' table name, for use as parameter $aTable with constructor.
	 * @global String $TABLE_WIDGET
	 */ 
	public static $TABLE_WIDGET = "widget";
	/**
	 * 'journal' table name, for use as parameter $aTable with constructor.
	 * @global String $TABLE_JOURNAL
	 */ 
	public static $TABLE_JOURNAL = "journal";
	/**
	 * 'location' table name, for use as parameter $aTable with constructor.
	 * @global String $TABLE_LOCATION
	 */ 
	public static $TABLE_LOCATION = "location";	
	/**
	 * 'project' table name, for use as parameter $aTable with constructor.
	 * @global String $TABLE_PROJECT
	 */ 
	public static $TABLE_PROJECT = "project";
	
	private $hostname = "";
	private $username = "";
	private $password = "";
	private $database = "";
	private $table = "";
	
        /**
        * Create a new TableAdapter linked to a table with the given name.
        *
        * @param String $anHostname hostname of database server
        * @param String $anUsername username for use with database server
        * @param String $aPassword password for user with database server
        * @param String $aDatabase name of database
        * @param String $aTable name of table, taken from static constant
        * @access public
        */        
	public function __construct($anHostname, $anUsername, $aPassword, $aDatabase, $aTable) {
	    $this->hostname = $anHostname;
	    $this->username = $anUsername;
	    $this->password = $aPassword;
	    $this->database = $aDatabase;
	    $this->table = $aTable;
	}
	
        // TODO: Move SQL generation to SQLStringBuilder class
        /**
        * Returns the row relating to the specified id within table.
        *
        * @param String $anIdentifierField name of (unique) identifier field
        * @param Integer $anIdentifierValue identifier value of row to return
        * @return DataSet row data
        * @access public
        */
	public function fetch($anIdentifierField, $anIdentifierValue) {
	    // query database
            $mysqli = new mysqli($this->hostname, $this->username, $this->password, $this->database);
            $query = "SELECT * FROM " . $this->table . " WHERE " . $anIdentifierField . " = " . $anIdentifierValue . ";";
            $result = $mysqli->query($query, $mysqli->MYSQL_NUM);
            $mysqli->close();
	    
	    // prepare result
	    if ($result) {
                // gather fields
                $columns = $this->getColumns($result);
		$resultSet = new DataSet($table, $anIdentifierField, $columns);
                // populate resultSet
                $resultSet->populate($result);
                // return result
                return $resultSet;
	    } else {
		return null;
	    }
	}
	
        /**
        * Returns all rows within table.
        *
        * @param $anIdentifierField name of (unique) identifier field
        * @return DataSet row data
        * @access public
        */
	public function fetchAll($anIdentifierField) {
	    // query database
            $mysqli = new mysqli($this->hostname, $this->username, $this->password, $this->database);
            $query = "SELECT * FROM " . $this->table . ";";
            $result = $mysqli->query($query, $mysqli->MYSQL_NUM);
            $mysqli->close();
	    
	    // prepare result
	    if ($result) {
                // gather fields
                $columns = $this->getColumns($result);
		$resultSet = new DataSet($table, $anIdentifierField, $columns);
                // populate resultSet
                $resultSet->populate($result);
                // return result
                return $resultSet;
	    } else {
		return null;
	    }
	}
        
        // TODO: Can half-commit, not atomic.
        /**
        * Commits DataSet to database. Only modified rows will be commited to
        * the database.
        *
        * @param $aDataSet DataSet to be commited
        * @return boolean true if all rows commited successfully, else false
        * @access public
        */
        public function commit($aDataSet) {
            $mysqli = new mysqli($this->hostname, $this->username, $this->password, $this->database);

            // prepare to count rows updated and ignored
            $rowsUpdated = 0;
            $rowsIgnored = 0;
      
            // aggregate SQL
            for ($rowCounter = 0; $rowCounter < $aDataSet->getRowCount(); $rowCounter++) {
                $row = $aDataSet->getRow($rowCounter);
                if ($row->isModified()) {
                    $query = "UPDATE " . $this->table . " SET ";
                    for ($columnCounter = 0; $columnCounter < $aDataSet->getColumnCount(); $columnCounter++) {
                        // skip identifier field
                        if ($columnCounter != $aDataSet->getIdentifierFieldIndex()) {
                            $value = $row->getValue($columnCounter);
                            $query .= $aDataSet->getColumnName($columnCounter) . " = ";
                            // encode value, depending on type
                            $query .= $this->encode($mysqli, $value) . ", ";
                        }
                    }
                    $query = substr($query, 0, strlen($query) - 2);
                    $query .= " WHERE " . $aDataSet->getIdentifierFieldName() . " = ";
                    $value = $row->getValue($aDataSet->getIdentifierFieldIndex());
                    // encode identifier value, depending on type
                    $query .= $this->encode($mysqli, $value) . ";";
                
                    // execute query
                    $rowsUpdated += $mysqli->query($query, $mysqli->MYSQL_NUM);
                } else {
                    $rowsIgnored++;
                }
            }

            // return result
            if (($rowsUpdated + $rowsIgnored) == $aDataSet->getRowCount()) {
                return true;
            } else {
                return false;
            }
            $mysqli->close();
        }
        
        /**
        * Returns encoded version of value, ready for use in SQL statement.
        *
        * @param $aConnection database connection to be used for encoding
        * @param $aValue value to be encoded
        * @return String encoded value, surrounded by quotation marks (if needed)
        * @access private
        */
        private function encode($connection, $aValue) {
            if ($aValue == null || $aValue == "") {
                return "NULL";
            } else if (is_numeric($aValue)) {
                return $aValue; 
            } else if (is_string($aValue)) {
                return "\"" . $connection->real_escape_string($aValue) . "\"";
            }
        }
        
        // TODO: below function not used
        /**
        * Returns list of tables within database.
        *
        * @return String[] list of tables
        * @access private
        */
        private function getTables() {
            $mysqli = new mysqli($this->hostname, $this->username, $this->password, $this->database);
            
            // get list of tables
            $query = "show tables;";
            $result = $mysqli->query($query, $mysqli->MYSQL_NUM);
            
            // gather row data into array
            $tables = array();
            while ($row = $result->fetch_array()) {
                array_push($tables, $row);
            }
            
            $mysqli->close();
            
            return $tables;
        }

        /**
        * Returns list of columns within a result set returned from a mysql or
        * mysqli query.
        *
        * @return String[] list of columns
        * @access private
        */
        private function getColumns($aDataSet) {
            // gather fields
            $fields = $aDataSet->fetch_fields();
            $columns = array();
            for ($fieldCounter = 0; $fieldCounter < count($fields); $fieldCounter++) {
                array_push($columns, $fields[$fieldCounter]->name);
            }
            return $columns;
        }
    }
?>